package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class PopularNamesDAO {
	
	//writes a pair popular name bird to the db. If already there, updates
	public static void writePopularName(PopularNamesVO popularName) throws SQLException{
		
		//first tries to write
		try{
			PreparedStatement comandoInsert = ConnectionManager.getConnection().prepareStatement("INSERT INTO popular_names VALUES (?, ?)");
			comandoInsert.setString(1, popularName.getName());
			comandoInsert.setString(2, popularName.getBird_scientific_name());
			comandoInsert.executeUpdate();
			
			//if write succeeds then it is over
			return;
			
		}
		
		catch (SQLException ex){
			
		}
		//if it fails, tries updating

		PreparedStatement comandoUpdate = ConnectionManager.getConnection().prepareStatement("UPDATE popular_names SET name = ?, bird_scientific_name = ? where name = ? and bird_scientific_name = ?");
		comandoUpdate.setString(1, popularName.getName());
		comandoUpdate.setString(2, popularName.getBird_scientific_name());
		comandoUpdate.setString(3, popularName.getName());
		comandoUpdate.setString(4, popularName.getBird_scientific_name());
		comandoUpdate.executeUpdate();
	}
	
	
	//returns all popular names of a bird, given its scientific name
	public static Vector<PopularNamesVO> selectByBird_scientific_name(String bird_scientific_name) throws SQLException{
		return arbitrarySQL("SELECT * FROM popular_names where bird_scientific_name = '"+bird_scientific_name+"'");
	}
	
	//returns all birds that have this popular name
	public static Vector<PopularNamesVO> selectByPopularName(String name) throws SQLException{
		return arbitrarySQL("SELECT * FROM popular_names where name = '"+name+"'");
	}
	
	//deletes all occurrences that have this bird
	public static void deleteByBird(String bird_scientific_name)throws SQLException{
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM popular_names WHERE bird_scientific_name = ?");
		comandoDelete.setString(1, bird_scientific_name);
		comandoDelete.executeUpdate();
	}
	
	//deletes a specific association between a bird and its popular name
	public static void deleteByScientificNameAndPopularName(String popularName, String bird_scientific_name) throws SQLException{
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM popular_names WHERE name = ? and bird_scientific_name = ?");
		comandoDelete.setString(1, popularName);
		comandoDelete.setString(2, bird_scientific_name);
		comandoDelete.executeUpdate();
	}
	
	//Deletes an instance of a PopularNamesVO from the DB
	public static void delete(PopularNamesVO popularNameObject) throws SQLException{
		deleteByScientificNameAndPopularName(popularNameObject.getName(), popularNameObject.getBird_scientific_name());
	}
	
	//deletes all occurrences with this popular name
	public static void deleteByName(String name) throws SQLException{
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM popular_names WHERE name = ?");
		comandoDelete.setString(1, name);
		comandoDelete.executeUpdate();
	}
	
	//Executes an arbitrary SQL command on Popular Names table
		public static Vector<PopularNamesVO> arbitrarySQL(String sql) throws SQLException{
			Vector<PopularNamesVO> PopularNames = new Vector<PopularNamesVO>();
			ResultSet resultados;
			PreparedStatement comandoSelect = ConnectionManager.getConnection().prepareStatement(sql);
			resultados = comandoSelect.executeQuery();
			
			while(resultados.next()){
				PopularNames.add(new PopularNamesVO(resultados.getString(1), resultados.getString(2)));
			}
			return PopularNames;
		}
		

}
